package com.tianqu.tms.www.finance.action;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.tianqu.tms.core.base.Logger;
import com.tianqu.tms.core.base.TmsAction;
import com.tianqu.tms.core.base.UserContext;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.core.util.TmsConstants;
import com.tianqu.tms.entity.TransferFinanceInfo;
import com.tianqu.tms.www.finance.viewhelper.FinanceSettlementSummaryViewHelper;

public class FinanceSettlementSummaryExportExcelAction extends TmsAction {
	private Logger logger = Logger.getLogger(FinanceSettlementSummaryExportExcelAction.class);

	@Override
	protected ActionForward doAction(ActionMapping mapping, ActionForm form, HttpServletRequest request,
			HttpServletResponse response, UserContext uc) throws Exception {
		logger.debug(this.getClass().getName() + "#execute was called.");
		
		String startDate = uc.getParameter("startDate");
		String endDate = uc.getParameter("endDate");
		String type = uc.getParameter("type");
		
		FinanceSettlementSummaryViewHelper viewhelper  = new FinanceSettlementSummaryViewHelper();
		if("1".equals(type)) {//已结算
			TransferFinanceInfo info = TransferFinanceInfo.getInstance().findTransferfinanceList(startDate.replace("-", ""), endDate.replace("-", ""));//已结算
			viewhelper.setInfo(info.getDetailInfo());
			
			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = wb.createSheet("业务结算汇总表查询结果");
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow((int) 0);
			// 第四步，创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			style.setBorderBottom((short) 1);
			style.setBorderTop((short) 1);
			style.setBorderLeft((short) 1);
			style.setBorderRight((short) 1);

			sheet.setDefaultRowHeightInPoints(10);
			sheet.setDefaultColumnWidth(17);

			HSSFFont font = wb.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			font.setFontHeightInPoints((short) 8);
			style.setFont(font);

			HSSFCellStyle cellStyle = wb.createCellStyle();
			HSSFFont titleFont = wb.createFont();
			titleFont.setFontName("宋体");
			titleFont.setFontHeight((short) 200);
			titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			cellStyle.setBorderBottom((short) 1);
			cellStyle.setBorderTop((short) 1);
			cellStyle.setBorderLeft((short) 1);
			cellStyle.setBorderRight((short) 1);
			cellStyle.setFont(titleFont);
			HSSFCell cell = row.createCell((short) 0);
			cell.setCellValue("序号");
			cell.setCellStyle(cellStyle);

			HSSFCell cell1 = row.createCell((short) 1);
			cell1.setCellValue("业务类型");
			cell1.setCellStyle(cellStyle);

			HSSFCell cell2 = row.createCell((short) 2);
			cell2.setCellValue("数量");
			cell2.setCellStyle(cellStyle);

			HSSFCell cell3 = row.createCell((short) 3);
			cell3.setCellValue("已结算金额");
			cell3.setCellStyle(cellStyle);
	 
			List<TransferFinanceInfo> list = viewhelper.getInfo();
			for (int i=1;i<list.size()+1;i++) {
				TransferFinanceInfo dto = (TransferFinanceInfo) list.get(i-1);
				row = sheet.createRow(i);
				HSSFCell c0 = row.createCell((short) 0);
				c0.setCellStyle(style);
				c0.setCellValue(dto.getNo());
				HSSFCell c1 = row.createCell((short) 1);
				c1.setCellStyle(style);
				c1.setCellValue(dto.getPriceTypeStr());
				
				HSSFCell c2 = row.createCell((short) 2);
				c2.setCellStyle(style);
				if(StringUtil.isNullOrBlank(dto.getTotal())) c2.setCellValue(0); 
				else c2.setCellValue(Integer.parseInt(dto.getTotal())); 
				
				HSSFCell c3 = row.createCell((short) 3);
				c3.setCellStyle(style);
				if(StringUtil.isNullOrBlank(dto.getTotal())) c3.setCellValue(0); 
				else c3.setCellValue(Double.parseDouble(dto.getPrice())); 
			}
			try {
				File file = new File(toUtf8String("业务结算汇总表查询结果"  +DateUtil.getCurrentTimeStr()+".xls"));
				OutputStream fout = new FileOutputStream(file);
				wb.write(fout);
				getResponse(file, response);
			} catch (Exception e) {
				e.printStackTrace();
			}
			request.setAttribute(TmsConstants.VIEWHELP, viewhelper);
		}else {//1 结算
			TransferFinanceInfo detail = TransferFinanceInfo.getInstance().findTransferFinanceDeail(startDate.replace("-", ""), endDate.replace("-", ""),Integer.parseInt(type),0);//未结算
			viewhelper.setDetails(detail.getDetailInfo());
			
			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = wb.createSheet("业务结算汇总表查询结果");
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow((int) 0);
			// 第四步，创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			style.setBorderBottom((short) 1);
			style.setBorderTop((short) 1);
			style.setBorderLeft((short) 1);
			style.setBorderRight((short) 1);
	
			sheet.setDefaultRowHeightInPoints(10);
			sheet.setDefaultColumnWidth(17);
	
			HSSFFont font = wb.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			font.setFontHeightInPoints((short) 8);
			style.setFont(font);
	
			HSSFCellStyle cellStyle = wb.createCellStyle();
			HSSFFont titleFont = wb.createFont();
			titleFont.setFontName("宋体");
			titleFont.setFontHeight((short) 200);
			titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	
			cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			cellStyle.setBorderBottom((short) 1);
			cellStyle.setBorderTop((short) 1);
			cellStyle.setBorderLeft((short) 1);
			cellStyle.setBorderRight((short) 1);
			cellStyle.setFont(titleFont);
			HSSFCell cell = row.createCell((short) 0);
			cell.setCellValue("序号");
			cell.setCellStyle(cellStyle);
	
			HSSFCell cell1 = row.createCell((short) 1);
			cell1.setCellValue("会员编号");
			cell1.setCellStyle(cellStyle);
	
			HSSFCell cell2 = row.createCell((short) 2);
			cell2.setCellValue("交费人");
			cell2.setCellStyle(cellStyle);
	
			HSSFCell cell3 = row.createCell((short) 3);
			cell3.setCellValue("所属商户");
			cell3.setCellStyle(cellStyle);
	
			HSSFCell cell4 = row.createCell((short) 4);
			cell4.setCellValue("业务类型");
			cell4.setCellStyle(cellStyle);
			
			HSSFCell cell5 = row.createCell((short) 5);
			cell5.setCellValue("费用类型");
			cell5.setCellStyle(cellStyle);
			
			HSSFCell cell6 = row.createCell((short) 6);
			cell6.setCellValue("金额");
			cell6.setCellStyle(cellStyle);
			
			HSSFCell cell7 = row.createCell((short) 7);
			cell7.setCellValue("操作员");
			cell7.setCellStyle(cellStyle);
			
			HSSFCell cell8 = row.createCell((short) 8);
			cell8.setCellValue("支付时间");
			cell8.setCellStyle(cellStyle);
	 
			HSSFCell cell9 = row.createCell((short) 9);
			cell9.setCellValue("结算时间");
			cell9.setCellStyle(cellStyle);
			
			List<TransferFinanceInfo> list = viewhelper.getDetails();
			for (int i=1;i<list.size()+1;i++) {
				TransferFinanceInfo dto = (TransferFinanceInfo) list.get(i-1);
				row = sheet.createRow(i);
				HSSFCell c0 = row.createCell((short) 0);
				c0.setCellStyle(style);
				c0.setCellValue(dto.getNo());
				HSSFCell c1 = row.createCell((short) 1);
				c1.setCellStyle(style);
				c1.setCellValue(dto.getSerial());
				HSSFCell c2 = row.createCell((short) 2);
				c2.setCellStyle(style);
				c2.setCellValue(dto.getAttnName());
				HSSFCell c3 = row.createCell((short) 3);
				c3.setCellStyle(style);
				c3.setCellValue(dto.getShortName());
				HSSFCell c4 = row.createCell((short) 4);
				c4.setCellStyle(style);
				c4.setCellValue(dto.getTransferType());
				
				HSSFCell c5 = row.createCell((short) 5);
				c5.setCellStyle(style);
				c5.setCellValue(dto.getPriceTypeStr());
				
				HSSFCell c6 = row.createCell((short) 6);
				c6.setCellStyle(style);
				if(StringUtil.isNullOrBlank(dto.getPrice())) c6.setCellValue(0); 
				else c6.setCellValue(Double.parseDouble(dto.getPrice())); 
				
				HSSFCell c7 = row.createCell((short) 7);
				c7.setCellStyle(style);
				c7.setCellValue(dto.getRealName());
				
				HSSFCell c8 = row.createCell((short) 8);
				c8.setCellStyle(style);
				c8.setCellValue(dto.getPriceDate());
				
				HSSFCell c9 = row.createCell((short) 9);
				c9.setCellStyle(style);
				c9.setCellValue(dto.getAcceptedTime());
			}
			try {
				File file = new File(toUtf8String("业务结算汇总表查询结果" +DateUtil.getCurrentTimeStr()+ ".xls"));
				OutputStream fout = new FileOutputStream(file);
				wb.write(fout);
				getResponse(file, response);
			} catch (Exception e) {
				e.printStackTrace();
			}
			request.setAttribute(TmsConstants.VIEWHELP, viewhelper);
		}
		
		return null;
	}
}
